package com.njcb.ams.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOperator;
import com.alibaba.druid.sql.ast.expr.SQLPropertyExpr;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser;
import com.alibaba.druid.sql.parser.SQLExprParser;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.util.JdbcUtils;
import com.njcb.ams.repository.entity.CommReportMeta;
import com.njcb.ams.store.page.Page;
import com.njcb.ams.store.page.PageHandle;
import com.njcb.ams.util.SqlParserUtils;

@Service
public class ReportService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public List<Map<String, Object>> queryForList(CommReportMeta commReportMeta, Map<String, Object> paramMap)  {
        Map<String, String> conditionMap = new HashMap<String, String>();
        List<SQLSelectItem> selectItems = SqlParserUtils.parserSelectItems(commReportMeta.getReportSql());
        selectItems.forEach(selectItem -> {
            if(selectItem.getExpr() instanceof SQLPropertyExpr){
                SQLPropertyExpr expr = (SQLPropertyExpr) selectItem.getExpr();
                String filedName = expr.getName();
                String filedAlias = null != selectItem.getAlias()?selectItem.getAlias():filedName;
                if(paramMap.containsKey(filedAlias)){
                    conditionMap.put(expr.toString(), (String) paramMap.get(filedAlias));
                }
            }else{
                String filedName = selectItem.getExpr().toString();
                String filedAlias = null != selectItem.getAlias()?selectItem.getAlias():filedName;
                if(paramMap.containsKey(filedAlias)){
                    conditionMap.put(filedAlias, (String) paramMap.get(filedAlias));
                }
            }
        });

        OracleStatementParser mySqlStatementParser = new OracleStatementParser(commReportMeta.getReportSql());
        SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) mySqlStatementParser.parseSelect();

        if (sqlSelectStatement instanceof SQLSelectStatement) {
            StringBuffer constraintsBuffer = new StringBuffer();
            conditionMap.forEach((key, value) -> {
                constraintsBuffer.append(key).append(" like ").append("'" + value + "'");
            });

            SQLSelect sqlSelect = sqlSelectStatement.getSelect();

            SQLSelectQueryBlock query = (SQLSelectQueryBlock) sqlSelect.getQuery();
            SQLExpr whereExpr = query.getWhere();
            if(conditionMap.size() > 0){
                SQLExprParser constraintsParser = SQLParserUtils.createExprParser(constraintsBuffer.toString(), JdbcUtils.ORACLE);
                SQLExpr constraintsExpr = constraintsParser.expr();
                if (null == whereExpr) {
                    query.setWhere(constraintsExpr);
                } else {
                    SQLBinaryOpExpr newWhereExpr = new SQLBinaryOpExpr(whereExpr, SQLBinaryOperator.BooleanAnd, constraintsExpr);
                    query.setWhere(newWhereExpr);
                }
            }

            List<Map<String, Object>> rt = new ArrayList<Map<String, Object>>();
            Page page = PageHandle.getPlatformPage();
            Integer count = count(sqlSelect.toString());
            if(null != page){
            	page.setTotal(count);
                rt = jdbcTemplate.queryForList(buildPageSql(sqlSelect.toString(), page));
            }else{
                rt = jdbcTemplate.queryForList(sqlSelect.toString());
            }
            return rt;
        }
        return null;
    }

    private String buildPageSql(String sql, Page page) {
        StringBuilder pageSql = new StringBuilder(200);
        pageSql.append("select * from ( select temp.*, rownum row_id from ( ");
        pageSql.append(sql);
        pageSql.append(" ) temp where rownum <= ").append(page.getEndRow());
        pageSql.append(") where row_id > ").append(page.getStartRow());
        return pageSql.toString();
    }


    private Integer count(String sql){
        String countSql = "select count(1) from (" + sql + ")";
        int count = jdbcTemplate.queryForObject(countSql, Integer.class);
        return count;
    }
}
